package org.jeecg.modules.demo.gmslopedata.controller;

/**
 * 功能描述
 *
 * @program: geology-monitor
 * @description:
 * @author: wwq
 * @create: 2025-07-18 10:13
 **/


import com.alibaba.fastjson.JSONObject;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.jeecg.common.api.vo.Result;
import org.jeecg.modules.demo.gmslopedata.entity.ShuZhiVo;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.*;

@Slf4j
@Api(tags = "PostgreSQL 空间查询接口")
@RestController
@RequestMapping("/postgresql/type1")
public class SockTypeController {

    // PostgreSQL 数据库配置
    @Value(value = "${gm.POSTGRESQL.JDBC_URL}")
    private String JDBC_URL;
    @Value(value = "${gm.POSTGRESQL.USERNAME}")
    private  String USERNAME ;

    @Value(value = "${gm.POSTGRESQL.PASSWORD}")
    private  String PASSWORD ;
    @Value(value = "${gm.POSTGRESQL.GEO_TABLE_NAME}")
    private  String GEO_TABLE_NAME ;

    @Value(value = "${gm.POSTGRESQL.SOCK_TABLE_NAME}")
    private  String SOCK_TABLE_NAME ;


    /* ========== Hikari 数据源(非 static) ========== */
    private DataSource dataSource;

    @PostConstruct
    public void initDataSource() {
        HikariConfig cfg = new HikariConfig();
        cfg.setJdbcUrl(JDBC_URL);
        cfg.setUsername(USERNAME);
        cfg.setPassword(PASSWORD);
        cfg.setMaximumPoolSize(10);
        cfg.setMinimumIdle(5);
        cfg.setConnectionTimeout(5_000);   // 获取连接最大等待
        cfg.setIdleTimeout(60_000);        // 空闲连接回收
        cfg.setMaxLifetime(30 * 60_000);   // 连接最大生命周期
        cfg.setConnectionTestQuery("SELECT 1");
        dataSource = new HikariDataSource(cfg);
        log.info("HikariCP 连接池初始化完成");
    }



    /* ========== 接口 ========== */
    @ApiOperation("根据坐标查询岩组类型")
    @PostMapping("/selectSockType")
    public Result<String> selectSockType(@RequestBody QueryPointRequest req) {
//        String sql = """
//            SELECT 岩类, 岩类名称
//            FROM   %s
//            WHERE  ST_Contains(
//                     geom,
//                     ST_Transform(
//                       ST_SetSRID(ST_MakePoint(?, ?), 4326),
//                       9999)
//                   )
//            """.formatted(SOCK_TABLE);
        double longitude = req.getLongitude();
        double latitude = req.getLatitude();
        double[] toWgs84 = Utils.transformGCJ2WGS(latitude,longitude);
//        System.out.println("toWgs84[0] = " + toWgs84[0]);
//        System.out.println("toWgs84[1] = " + toWgs84[1]);
        // 使用参数化SQL防止注入
        final String sql = "SELECT  岩类, 岩类名称 " +
                "FROM  " +SOCK_TABLE_NAME+
                " WHERE ST_Contains(geom, " +
                "  ST_Transform(" +
                "    ST_SetSRID(ST_MakePoint(?, ?), 4326)," +
                "    9999" +
                "  )" +
                ")";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setDouble(1,toWgs84[1]);
            ps.setDouble(2, toWgs84[0]);
//            ps.setDouble(2, req.getLongitude());
//            ps.setDouble(1, req.getLatitude());
            ps.setQueryTimeout(5);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    String type = rs.getString("岩类") + " " + rs.getString("岩类名称").trim();
                    log.info("岩组类型查询结果: {}", type);
                    return Result.ok(type);
                }
                log.warn("未找到岩组类型记录: {}", req);
                return Result.ok("未找到匹配记录");
            }

        } catch (SQLException e) {
            log.error("岩组类型查询异常", e);
            return Result.error("数据库异常: " + e.getMessage());
        }
    }

    @ApiOperation("根据坐标查询坡度类型")
    @PostMapping("/selectGeoType")
    public Result<String> selectGeoType(@RequestBody QueryPointRequest req) {
//        String sql = """
//            SELECT gridcode
//            FROM   %s
//            WHERE  ST_Intersects(
//                     the_geom,
//                     ST_SetSRID(ST_MakePoint(?, ?), 4326)
//                   )
//            LIMIT 1
//            """.formatted(GEO_TABLE);
        double longitude = req.getLongitude();
        double latitude = req.getLatitude();
        double[] toWgs84 = Utils.transformGCJ2WGS(latitude,longitude);
//        System.out.println("toWgs84[0] = " + toWgs84[0]);
//        System.out.println("toWgs84[1] = " + toWgs84[1]);
        final String sql = "SELECT id, gridcode " +
                "FROM " +GEO_TABLE_NAME+
                " WHERE ST_Intersects(" +
                "  the_geom, " +
                "  ST_SetSRID(ST_MakePoint(?, ?), 4326)" +
                ") LIMIT 1";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

//            ps.setDouble(2, req.getLongitude());
//            ps.setDouble(1, req.getLatitude());
            ps.setDouble(1,toWgs84[1]);
            ps.setDouble(2, toWgs84[0]);
            ps.setQueryTimeout(5);
//            System.out.println("sql = " + sql);
            String result = "";
            try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                String slopeValue =rs.getString("gridcode");
                switch (slopeValue) {
                    case "1":
                        result = "A 小于20°".trim();
                        break;
                    case "2":
                        result = "B 20-30°".trim();
                        break;
                    case "3":
                        result = "C 大于30°".trim();
                        break;
                    default:
                        result = "未知坡度类型";
                }

               log.info("找到匹配记录: " + result);
                return Result.ok(result);
            }
                log.warn("未找到岩组类型记录: {}", req);
                return Result.ok("A 小于20°");   // 与旧逻辑保持一致
            }

        } catch (SQLException e) {
            log.error("坡度类型查询异常", e);
            return Result.error("数据库异常: " + e.getMessage());
        }
    }

    /* ========== 接口 ========== */
    @ApiOperation("根据坐标数字孪生")
    @PostMapping("/selectShuZhi")
    public Result<ShuZhiVo> selectShuZhi(@RequestBody JSONObject  req) {
//        String sql = """
//            SELECT 岩类, 岩类名称
//            FROM   %s
//            WHERE  ST_Contains(
//                     geom,
//                     ST_Transform(
//                       ST_SetSRID(ST_MakePoint(?, ?), 4326),
//                       9999)
//                   )
//            """.formatted(SOCK_TABLE);
        String uuid = req.getString("uuid");
//        System.out.println("toWgs84[0] = " + toWgs84[0]);
//        System.out.println("toWgs84[1] = " + toWgs84[1]);
        // 使用参数化SQL防止注入
        final String sql = "SELECT * " +
                "FROM aircity.aircity_propertyclass_1 " +
                "WHERE uuid = ?";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
             ps.setString(1, uuid);
//            ps.setDouble(2, req.getLongitude());
//            ps.setDouble(1, req.getLatitude());
            ps.setQueryTimeout(5);
            ShuZhiVo shuZhiVo = new ShuZhiVo();
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    String modelhash = rs.getString("modelhash");
                    shuZhiVo.setUuid(uuid);
                    shuZhiVo.setModelhash(modelhash);
                    String file = rs.getString("file");
                    shuZhiVo.setFile(file);
                    String parentid = rs.getString("parentid");
                    shuZhiVo.setParentid(parentid);
                    byte[] properties = rs.getBytes("properties");
                    shuZhiVo.setProperties(properties);
                    String geom = rs.getString("geom");
                    shuZhiVo.setGeom(geom);

                    return Result.ok(shuZhiVo);
                }
                log.warn("未找到记录: {}", req);
                return Result.ok("未找到匹配记录");
            }

        } catch (SQLException e) {
            log.error("岩组类型查询异常", e);
            return Result.error("数据库异常: " + e.getMessage());
        }
    }







    /* ========== 请求 DTO ========== */
    @Data
    public static class QueryPointRequest {
        private double longitude;
        private double latitude;
    }



}
